package com.gitee.zzh.common;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 数据库工具
 */
public class DBUtil {
	private static final Logger LOG = LoggerFactory.getLogger(DBUtil.class);
	private static final int DB_CONNECTION_TIMEOUTS_SECONDS = 1;

	/**
	 * 获得数据库连接
	 */
	public static Connection getConnection(DatabaseConfig config) throws ClassNotFoundException, SQLException {
		DriverManager.setLoginTimeout(DB_CONNECTION_TIMEOUTS_SECONDS);
		DBType dbType = DBType.valueOf(config.getDbType());
		Class.forName(dbType.getDriverClass());
		String url = getConnectionURL(config);
		if (dbType == DBType.Oracle) {
			Connection connection;
			try {
				connection = DriverManager.getConnection(url, config.getUserName(), config.getUserPwd());
			} catch (Exception e) {
				String oracle = String.format(DBType.OracleServiceName.getConnectionUrlPattern(), config.getConnURL(), config.getListenPort(),
						config.getDbName());
				connection = DriverManager.getConnection(oracle, config.getUserName(), config.getUserPwd());
			}
			return connection;
		} else {
			return DriverManager.getConnection(url, config.getUserName(), config.getUserPwd());
		}
	}

	/**
	 * 获得数据库连接URL
	 */
	public static String getConnectionURL(DatabaseConfig dbConfig) throws ClassNotFoundException {
		DBType dbType = DBType.valueOf(dbConfig.getDbType());
		return String.format(dbType.getConnectionUrlPattern(), dbConfig.getConnURL(), dbConfig.getListenPort(),
				dbConfig.getDbName(), dbConfig.getEncoding());
	}

	/**
	 * 获得数据库的表名
	 */
	public static List<String> getTableNames(DatabaseConfig config) throws Exception {
		Connection conn = getConnection(config);
		List<String> tables = new ArrayList<>();
		ResultSet rs;
		if (config.getDbType().equalsIgnoreCase(DbConstant.SQL_SERVER)) {
			// 如果是sqlserver数据库通过查询获得所有表跟视图
			String sql = "select name from sysobjects where UPPER(xtype)='U' or UPPER(xtype)='V'";
			rs = conn.createStatement().executeQuery(sql);
			while (rs.next()) {
				tables.add(rs.getString("name"));
			}

		} else {
			// 如果非sqlserver类型的数据库通过JDBC获得所有表跟视图
			DatabaseMetaData md = conn.getMetaData();
			String[] types = {"TABLE", "VIEW"};
			if (config.getDbType().equalsIgnoreCase(DbConstant.POSTGRE_SQL)) {
				rs = md.getTables(null, null, null, types);
			} else {
				String catalog = conn.getCatalog() == null ? null : conn.getCatalog();
				rs = md.getTables(catalog, config.getUserName().toUpperCase(), "%%", types);
			}
			while (rs.next()) {
				tables.add(rs.getString(3));
			}
		}
		return tables;
	}

	/**
	 * 获得指定表的属性
	 */
	public static String getTableAttribute(DatabaseConfig config, String tableName) throws Exception {
		Connection conn = getConnection(config);
		ResultSet rs;
		DatabaseMetaData md = conn.getMetaData();
		String[] types = {"TABLE", "VIEW"};
		if (config.getDbType().equalsIgnoreCase(DbConstant.POSTGRE_SQL)) {
			rs = md.getTables(null, null, tableName, types);
		} else {
			String catalog = conn.getCatalog() == null ? null : conn.getCatalog();
			rs = md.getTables(catalog, config.getUserName().toUpperCase(), tableName, types);
		}
		if (rs.next()) {
			return rs.getString("REMARKS");
		}
		return null;
	}

	/**
	 * 获取表的列属性
	 */
	public static List<Map<String, String>> getTableColumns(DatabaseConfig config, String tableName) throws Exception {
		Connection conn = getConnection(config);
		DatabaseMetaData md = conn.getMetaData();

		ResultSet rs = null;
		if (config.getDbType().equalsIgnoreCase(DbConstant.MYSQL)) {
			rs = md.getColumns(conn.getCatalog(), "%%", tableName, "%%");
		} else {
			rs = md.getColumns(null, null, tableName, null);
		}
		List<Map<String, String>> list = new ArrayList<>();
		while (rs.next()) {
			Map<String, String> map = new HashMap<>();
			map.put("COLUMN_NAME", rs.getString("COLUMN_NAME"));
			map.put("TYPE_NAME", rs.getString("TYPE_NAME"));
//			map.put("COLUMN_DEF", rs.getString("COLUMN_DEF"));
			map.put("REMARKS", rs.getString("REMARKS"));
//			map.put("COLUMN_SIZE", rs.getInt("COLUMN_SIZE"));
//			map.put("DECIMAL_DIGITS", rs.getInt("DECIMAL_DIGITS"));
//			map.put("ORDINAL_POSITION", rs.getInt("ORDINAL_POSITION"));
//			map.put("NULLABLE", rs.getInt("NULLABLE"));
			list.add(map);
		}
		return list;
	}

	/**
	 * 获得主键名称
	 */
	public static List<String> getTablePrimaryKey(DatabaseConfig config, String tableName) throws Exception {
		Connection conn = getConnection(config);
		DatabaseMetaData md = conn.getMetaData();
		ResultSet rs = null;
		if (config.getDbType().equalsIgnoreCase(DbConstant.MYSQL)) {
			rs = md.getPrimaryKeys(conn.getCatalog(), conn.getSchema(), tableName);
		} else {
			rs = md.getPrimaryKeys(null, null, tableName);
		}
		List<String> list = new ArrayList<>();
		while (rs.next()) {
			list.add(rs.getString("COLUMN_NAME"));
		}
		return list;
	}

}
